# 一、创建ODS的customer_appeal线索申诉表
CREATE EXTERNAL TABLE IF NOT EXISTS itcast_ods.customer_appeal (
  id int COMMENT 'customer_appeal_id',
  customer_relationship_first_id int COMMENT '第一条客户关系id',
  employee_id int COMMENT '申诉人',
  employee_name STRING COMMENT '申诉人姓名',
  employee_department_id int COMMENT '申诉人部门',
  employee_tdepart_id int COMMENT '申诉人所属部门',
  appeal_status int COMMENT '申诉状态，0:待稽核 1:无效 2：有效',
  audit_id int COMMENT '稽核人id',
  audit_name STRING COMMENT '稽核人姓名',
  audit_department_id int COMMENT '稽核人所在部门',
  audit_department_name STRING COMMENT '稽核人部门名称',
  audit_date_time STRING COMMENT '稽核时间',
  create_date_time STRING COMMENT '创建时间（申诉时间）',
  update_date_time STRING COMMENT '更新时间',
  deleted STRING COMMENT '删除标志位',
  tenant int COMMENT '租户id') 
comment '客户申诉表'
PARTITIONED BY(start_time STRING)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='SNAPPY','orc.create.index'='true','orc.bloom.filter.columns'='appeal_status,customer_relationship_first_id');

# 二、使用Sqoop导入数据到ODS表中
sqoop import \
--connect jdbc:mysql://192.168.52.150:3306/scrm \
--username root \
--password 123456 \
--query 'select `id`,`customer_relationship_first_id`,`employee_id`,`employee_name`,`employee_department_id`,`employee_tdepart_id`,`appeal_status`,`audit_id`,`audit_name`,`audit_department_id`,`audit_department_name`,`audit_date_time`,`create_date_time`,`update_date_time`,`deleted`,`tenant`,DATE_SUB(curdate(),INTERVAL 1 DAY) as start_time from customer_appeal where $CONDITIONS' \
--hcatalog-database itcast_ods \
--hcatalog-table customer_appeal \
-m 2 \
--split-by id
